"""项目数据库服务"""
import mysql.connector
from mysql.connector import Error
from datetime import datetime
from typing import List, Dict, Any
from config.db_config import DB_CONFIG
from utils.logger import sync_logger

class ProjectDBService:
    def __init__(self):
        self.config = DB_CONFIG
        self._init_tables()
    
    def _init_tables(self):
        """初始化数据库表"""
        try:
            connection = self.get_connection()
            if connection.is_connected():
                cursor = connection.cursor()
                
                # 删除旧表（如果存在）
                cursor.execute("DROP TABLE IF EXISTS commits")  # 先删除commits表
                cursor.execute("DROP TABLE IF EXISTS project_activity_scores")
                cursor.execute("DROP TABLE IF EXISTS project_statistics")
                cursor.execute("DROP TABLE IF EXISTS projects")
                
                # 创建项目基本信息表
                cursor.execute("""
                    CREATE TABLE IF NOT EXISTS projects (
                        id INT PRIMARY KEY,
                        name VARCHAR(255) NOT NULL,
                        description TEXT,
                        created_at DATETIME,
                        last_activity_at DATETIME,
                        web_url VARCHAR(255),
                        default_branch VARCHAR(100),
                        readme_url VARCHAR(255),
                        star_count INT DEFAULT 0,
                        forks_count INT DEFAULT 0,
                        visibility VARCHAR(20),
                        open_issues_count INT DEFAULT 0,
                        repository_size BIGINT DEFAULT 0,
                        updated_at DATETIME
                    )
                """)
                
                # 创建项目统计信息表
                cursor.execute("""
                    CREATE TABLE IF NOT EXISTS project_statistics (
                        id INT AUTO_INCREMENT PRIMARY KEY,
                        project_id INT NOT NULL,
                        total_commits INT DEFAULT 0,
                        total_branches INT DEFAULT 0,
                        total_tags INT DEFAULT 0,
                        total_contributors INT DEFAULT 0,
                        total_merge_requests INT DEFAULT 0,
                        open_merge_requests INT DEFAULT 0,
                        closed_merge_requests INT DEFAULT 0,
                        merged_merge_requests INT DEFAULT 0,
                        total_issues INT DEFAULT 0,
                        open_issues INT DEFAULT 0,
                        closed_issues INT DEFAULT 0,
                        code_coverage FLOAT DEFAULT 0.0,
                        stats_date DATE NOT NULL,
                        FOREIGN KEY (project_id) REFERENCES projects(id),
                        UNIQUE KEY unique_project_date (project_id, stats_date)
                    )
                """)
                
                # 创建项目活跃度评分表
                cursor.execute("""
                    CREATE TABLE IF NOT EXISTS project_activity_scores (
                        id INT AUTO_INCREMENT PRIMARY KEY,
                        project_id INT NOT NULL,
                        commit_frequency_score FLOAT DEFAULT 0.0,
                        mr_frequency_score FLOAT DEFAULT 0.0,
                        issue_handling_score FLOAT DEFAULT 0.0,
                        contributor_diversity_score FLOAT DEFAULT 0.0,
                        code_review_score FLOAT DEFAULT 0.0,
                        total_score FLOAT DEFAULT 0.0,
                        score_date DATE NOT NULL,
                        FOREIGN KEY (project_id) REFERENCES projects(id),
                        UNIQUE KEY unique_project_score_date (project_id, score_date)
                    )
                """)
                
                # 重新创建commits表
                cursor.execute("""
                    CREATE TABLE IF NOT EXISTS commits (
                        id VARCHAR(255) PRIMARY KEY,
                        project_id INT NOT NULL,
                        title VARCHAR(255),
                        message TEXT,
                        author_name VARCHAR(255),
                        author_email VARCHAR(255),
                        authored_date DATETIME,
                        committer_name VARCHAR(255),
                        committer_email VARCHAR(255),
                        committed_date DATETIME,
                        web_url VARCHAR(255),
                        stats_additions INT DEFAULT 0,
                        stats_deletions INT DEFAULT 0,
                        stats_total INT DEFAULT 0,
                        FOREIGN KEY (project_id) REFERENCES projects(id)
                    )
                """)
                
                connection.commit()
                sync_logger.log_info("项目相关数据表初始化成功")
                
        except Error as e:
            sync_logger.log_error(f"初始化项目相关数据表时发生错误: {e}")
        finally:
            if connection.is_connected():
                cursor.close()
                connection.close()

    def get_connection(self):
        """获取数据库连接"""
        return mysql.connector.connect(**self.config)
    
    def _convert_datetime(self, dt_str: str) -> datetime:
        """转换日期时间字符串为MySQL兼容格式"""
        if not dt_str:
            return None
        try:
            # 移除末尾的Z并转换为datetime对象
            dt_str = dt_str.rstrip('Z')
            dt = datetime.fromisoformat(dt_str)
            return dt
        except (ValueError, TypeError):
            sync_logger.log_error(f"日期时间转换失败: {dt_str}")
            return None

    def save_project(self, project_data: Dict[str, Any]):
        """保存项目基本信息"""
        try:
            connection = self.get_connection()
            if connection.is_connected():
                cursor = connection.cursor()
                
                # 转换日期时间字段
                created_at = self._convert_datetime(project_data.get('created_at'))
                last_activity_at = self._convert_datetime(project_data.get('last_activity_at'))
                
                sql = """
                    INSERT INTO projects (
                        id, name, description, created_at, last_activity_at,
                        web_url, default_branch, readme_url, star_count,
                        forks_count, visibility, open_issues_count,
                        repository_size, updated_at
                    ) VALUES (
                        %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s
                    ) ON DUPLICATE KEY UPDATE
                        name = VALUES(name),
                        description = VALUES(description),
                        last_activity_at = VALUES(last_activity_at),
                        web_url = VALUES(web_url),
                        default_branch = VALUES(default_branch),
                        readme_url = VALUES(readme_url),
                        star_count = VALUES(star_count),
                        forks_count = VALUES(forks_count),
                        visibility = VALUES(visibility),
                        open_issues_count = VALUES(open_issues_count),
                        repository_size = VALUES(repository_size),
                        updated_at = VALUES(updated_at)
                """
                
                values = (
                    project_data['id'],
                    project_data['name'],
                    project_data.get('description', ''),
                    created_at,
                    last_activity_at,
                    project_data.get('web_url', ''),
                    project_data.get('default_branch', ''),
                    project_data.get('readme_url', ''),
                    project_data.get('star_count', 0),
                    project_data.get('forks_count', 0),
                    project_data.get('visibility', ''),
                    project_data.get('open_issues_count', 0),
                    project_data.get('repository_size', 0),
                    datetime.now()
                )
                
                cursor.execute(sql, values)
                connection.commit()
                sync_logger.log_info(f"成功保存项目 {project_data['id']} 的基本信息")
                
        except Error as e:
            sync_logger.log_error(f"保存项目 {project_data['id']} 基本信息时发生错误: {e}")
            raise  # 重新抛出异常，让调用者知道保存失败
        finally:
            if connection.is_connected():
                cursor.close()
                connection.close()
    
    def save_project_statistics(self, stats: Dict[str, Any]):
        """保存项目统计信息"""
        try:
            connection = self.get_connection()
            if connection.is_connected():
                cursor = connection.cursor()
                
                sql = """
                    INSERT INTO project_statistics (
                        project_id, total_commits, total_branches, total_tags,
                        total_contributors, total_merge_requests, open_merge_requests,
                        closed_merge_requests, merged_merge_requests, total_issues,
                        open_issues, closed_issues, code_coverage, stats_date
                    ) VALUES (
                        %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s
                    ) ON DUPLICATE KEY UPDATE
                        total_commits = VALUES(total_commits),
                        total_branches = VALUES(total_branches),
                        total_tags = VALUES(total_tags),
                        total_contributors = VALUES(total_contributors),
                        total_merge_requests = VALUES(total_merge_requests),
                        open_merge_requests = VALUES(open_merge_requests),
                        closed_merge_requests = VALUES(closed_merge_requests),
                        merged_merge_requests = VALUES(merged_merge_requests),
                        total_issues = VALUES(total_issues),
                        open_issues = VALUES(open_issues),
                        closed_issues = VALUES(closed_issues),
                        code_coverage = VALUES(code_coverage)
                """
                
                values = (
                    stats['project_id'],
                    stats.get('total_commits', 0),
                    stats.get('total_branches', 0),
                    stats.get('total_tags', 0),
                    stats.get('total_contributors', 0),
                    stats.get('total_merge_requests', 0),
                    stats.get('open_merge_requests', 0),
                    stats.get('closed_merge_requests', 0),
                    stats.get('merged_merge_requests', 0),
                    stats.get('total_issues', 0),
                    stats.get('open_issues', 0),
                    stats.get('closed_issues', 0),
                    stats.get('code_coverage', 0.0),
                    stats['stats_date']
                )
                
                cursor.execute(sql, values)
                connection.commit()
                sync_logger.log_info(f"成功保存项目 {stats['project_id']} 的统计信息")
                
        except Error as e:
            sync_logger.log_error(f"保存项目 {stats['project_id']} 统计信息时发生错误: {e}")
        finally:
            if connection.is_connected():
                cursor.close()
                connection.close()
    
    def save_activity_scores(self, scores: Dict[str, Any]):
        """保存项目活跃度评分"""
        try:
            connection = self.get_connection()
            if connection.is_connected():
                cursor = connection.cursor()
                
                sql = """
                    INSERT INTO project_activity_scores (
                        project_id, commit_frequency_score, mr_frequency_score,
                        issue_handling_score, contributor_diversity_score,
                        code_review_score, total_score, score_date
                    ) VALUES (
                        %s, %s, %s, %s, %s, %s, %s, %s
                    ) ON DUPLICATE KEY UPDATE
                        commit_frequency_score = VALUES(commit_frequency_score),
                        mr_frequency_score = VALUES(mr_frequency_score),
                        issue_handling_score = VALUES(issue_handling_score),
                        contributor_diversity_score = VALUES(contributor_diversity_score),
                        code_review_score = VALUES(code_review_score),
                        total_score = VALUES(total_score)
                """
                
                values = (
                    scores['project_id'],
                    scores.get('commit_frequency_score', 0.0),
                    scores.get('mr_frequency_score', 0.0),
                    scores.get('issue_handling_score', 0.0),
                    scores.get('contributor_diversity_score', 0.0),
                    scores.get('code_review_score', 0.0),
                    scores.get('total_score', 0.0),
                    scores['score_date']
                )
                
                cursor.execute(sql, values)
                connection.commit()
                sync_logger.log_info(f"成功保存项目 {scores['project_id']} 的活跃度评分")
                
        except Error as e:
            sync_logger.log_error(f"保存项目 {scores['project_id']} 活跃度评分时发生错误: {e}")
        finally:
            if connection.is_connected():
                cursor.close()
                connection.close() 